import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'dart:io';

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  static Database? _database;

  // 单例模式
  factory DatabaseService() => _instance;

  DatabaseService._internal();

  // 数据库版本
  static const _databaseVersion = 2;
  static const _databaseName = 'meco.db';

  // 获取数据库单例
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  // 初始化数据库
  Future<Database> _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  // 数据库升级
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // 版本1升级到版本2：添加is_completed字段
      await db.execute(
        'ALTER TABLE ceramics ADD COLUMN is_completed INTEGER NOT NULL DEFAULT 0',
      );
    }
  }

  // 创建表
  Future<void> _onCreate(Database db, int version) async {
    // 用户表
    await db.execute('''
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        nickname TEXT,
        avatar TEXT,
        password TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');

    // 陶瓷作品表
    await db.execute('''
      CREATE TABLE ceramics (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        title TEXT,
        description TEXT,
        image_url TEXT,
        is_skipped INTEGER NOT NULL DEFAULT 0,
        is_completed INTEGER NOT NULL DEFAULT 0,
        like_count INTEGER NOT NULL DEFAULT 0,
        favorite_count INTEGER NOT NULL DEFAULT 0,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
      )
    ''');

    // 陶瓷烧制阶段表
    await db.execute('''
      CREATE TABLE ceramic_stages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ceramic_id INTEGER NOT NULL,
        type INTEGER NOT NULL,
        status INTEGER NOT NULL,
        duration_seconds INTEGER NOT NULL DEFAULT 0,
        start_time INTEGER,
        end_time INTEGER,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        FOREIGN KEY (ceramic_id) REFERENCES ceramics (id) ON DELETE CASCADE
      )
    ''');

    // 点赞表
    await db.execute('''
      CREATE TABLE ceramic_likes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        ceramic_id INTEGER NOT NULL,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        UNIQUE(user_id, ceramic_id),
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
        FOREIGN KEY (ceramic_id) REFERENCES ceramics (id) ON DELETE CASCADE
      )
    ''');

    // 收藏表
    await db.execute('''
      CREATE TABLE ceramic_favorites (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        ceramic_id INTEGER NOT NULL,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        UNIQUE(user_id, ceramic_id),
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
        FOREIGN KEY (ceramic_id) REFERENCES ceramics (id) ON DELETE CASCADE
      )
    ''');

    // 官方内容表
    await db.execute('''
      CREATE TABLE official_contents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        image_url TEXT,
        publish_time INTEGER NOT NULL,
        is_active INTEGER NOT NULL DEFAULT 1,
        tag TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');

    // 推荐视频表
    await db.execute('''
      CREATE TABLE recommended_videos (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
        description TEXT NOT NULL,
        thumbnail_url TEXT,
        video_url TEXT NOT NULL,
          like_count INTEGER NOT NULL DEFAULT 0,
          favorite_count INTEGER NOT NULL DEFAULT 0,
          created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
        )
      ''');

    // 视频点赞表
    await db.execute('''
      CREATE TABLE video_likes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        video_id INTEGER NOT NULL,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL,
        UNIQUE(user_id, video_id),
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
        FOREIGN KEY (video_id) REFERENCES recommended_videos (id) ON DELETE CASCADE
        )
      ''');

    // 视频收藏表
    await db.execute('''
      CREATE TABLE video_favorites (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER NOT NULL,
        video_id INTEGER NOT NULL,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL,
        UNIQUE(user_id, video_id),
          FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
        FOREIGN KEY (video_id) REFERENCES recommended_videos (id) ON DELETE CASCADE
        )
      ''');

    // 浏览记录表
    await db.execute('''
      CREATE TABLE browse_records (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER NOT NULL,
        content_id INTEGER NOT NULL,
        type INTEGER NOT NULL,
        browse_time INTEGER NOT NULL,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
      ''');

    // 用户关注表
    await db.execute('''
      CREATE TABLE user_follows (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
        follower_id INTEGER NOT NULL,
        following_id INTEGER NOT NULL,
          created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        UNIQUE(follower_id, following_id),
        FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE,
        FOREIGN KEY (following_id) REFERENCES users (id) ON DELETE CASCADE
      )
    ''');

    // 评论表
    await db.execute('''
      CREATE TABLE comments (
        id TEXT PRIMARY KEY,
        content TEXT NOT NULL,
        user_id TEXT NOT NULL,
        user_name TEXT NOT NULL,
        user_avatar TEXT NOT NULL,
        content_id TEXT NOT NULL,
        type INTEGER NOT NULL,
        like_count INTEGER DEFAULT 0,
        created_at TEXT NOT NULL
        )
      ''');

    // 聊天室表
    await db.execute('''
      CREATE TABLE chat_rooms (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        is_group INTEGER NOT NULL DEFAULT 0,
        member_ids TEXT NOT NULL,
        last_message TEXT,
        last_message_time INTEGER,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');

    // 聊天消息表
    await db.execute('''
      CREATE TABLE chat_messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        chat_room_id INTEGER NOT NULL,
        sender_id INTEGER NOT NULL,
        content TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        read_by TEXT,
        FOREIGN KEY (chat_room_id) REFERENCES chat_rooms (id) ON DELETE CASCADE,
        FOREIGN KEY (sender_id) REFERENCES users (id) ON DELETE CASCADE
      )
    ''');
  }

  // 关闭数据库连接
  Future<void> close() async {
    if (_database != null) {
      await _database!.close();
      _database = null;
      print('数据库连接已关闭');
    }
  }

  // 清除所有数据（删除并重新创建数据库）
  Future<void> clearDatabase() async {
    try {
      print('开始清除数据库...');

      // 先关闭数据库连接
      await close();

      // 获取数据库文件路径
      Directory documentsDirectory = await getApplicationDocumentsDirectory();
      String path = join(documentsDirectory.path, _databaseName);

      // 删除数据库文件
      if (await File(path).exists()) {
        await deleteDatabase(path);
        print('数据库文件已删除');
      } else {
        print('数据库文件不存在，无需删除');
      }

      // 确保内存中的引用被清除
      _database = null;

      // 短暂延迟以确保文件系统操作完成
      await Future.delayed(Duration(milliseconds: 100));

      // 重新初始化数据库
      _database = await _initDatabase();
      print('数据库已重新初始化');
    } catch (e) {
      print('清除数据库过程中发生错误: $e');
      // 确保任何错误情况下，数据库引用被重置
      _database = null;

      // 最后尝试重新初始化数据库
      try {
        _database = await _initDatabase();
        print('尽管出错，数据库已重新初始化');
      } catch (e2) {
        print('重新初始化数据库失败: $e2');
      }
    }
  }
}
